﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HostAssistantCore
{
   public   class MySqlDbHelper
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;
        private string port;

        public  void Initialize(string server, string database, string uid, string password, string port)
        {
            this.server = server;
            this.uid = uid;
            this.password = password;
            this.port = port;
            this.database = database;
            string connectionString = "Data Source=" + server + ";" + "port=" + port + ";" + "Database=" + database + ";" + "User Id=" + uid + ";" + "Password=" + password + ";" + "CharSet = utf8"; ;
            connection = new MySqlConnection(connectionString);
        }


      
        public string OpenConnection()
        {
            string r = null;
            try
            {
                if (ConnectionState.Open != connection.State)
                {
                    connection.Open();
                }
                return r;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based on the error number.  
                //The two most common error numbers when connecting are as follows:  
                //0: Cannot connect to server.  
                //1045: Invalid user name and/or password.  
                switch (ex.Number)
                {
                    case 0:
                      r="Cannot connect to server.  Contact administrator";

                        break;

                    case 1045:
                      
                      r="Invalid username/password, please try again";


                        break;
                }
                return r;
            }
        }
        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public string ConnectionTest()
        {
            string r = null;
            try
            {
                if (ConnectionState.Open != connection.State)
                {
                    connection.Open();
                    CloseConnection();
                }
                return r;
            }
            catch (Exception ex) //(MySqlException ex)
            {
                NLogger.Error("测试数据库连接(mysql):"+ex.ToString());
                r = ex.Message;
                //When handling errors, you can your application's response based on the error number.  
                //The two most common error numbers when connecting are as follows:  
                //0: Cannot connect to server.  
                //1045: Invalid user name and/or password.  
                //switch (ex.Number)
                //{
                //    case 0:
                //        MessageBox.Show("Cannot connect to server.  Contact administrator");
                //        break;

                //    case 1045:
                //        MessageBox.Show("Invalid username/password, please try again");
                //        break;
                //}
                return r;
            }
        }

        //Close connection  
        public string CloseConnection()
        {
            string r = null;
            try
            {
                if (ConnectionState.Open == connection.State)
                    connection.Close();
                return r;
            }
            catch (MySqlException ex)
            {
              

               r="Invalid username/password, please try again";


                return r;
            }
        }

        public DataTable GetSchema(string str, string[] restri)
        {
            return connection.GetSchema(str, restri);
        }
        public DataTable GetSchema(string str)
        {
            return connection.GetSchema(str);
        }
        // Get Database List  

        //Insert statement  
        public void Insert()
        {
            string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";

            //open connection  
            if (this.OpenConnection() == null)
            {
                //create command and assign the query and connection from the constructor  
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Execute command  
                cmd.ExecuteNonQuery();
                //close connection  
                this.CloseConnection();
            }
        }

        //Update statement  
        public bool Update(string query)
        {
            bool flag = true;
            try
            {
                //Open connection  
                if (this.OpenConnection() == null)
                {
                    //create mysql command  
                    MySqlCommand cmd = new MySqlCommand();
                    //Assign the query using CommandText  
                    cmd.CommandText = query;
                    //Assign the connection using Connection  
                    cmd.Connection = connection;

                    //Execute query  
                    if (cmd.ExecuteNonQuery() == 0)
                    {
                        flag = false;
                    }

                    //close connection  
                    this.CloseConnection();
                }
            }
            catch (Exception ex )
            {
                NLogger.Error(ex.ToString());
                flag = false;
            }
            return flag;
        }
        //simple statement  
        public bool Touch(string query)
        {
            bool flag = true;
            try
            {
                //Open connection  
                if (this.OpenConnection() == null)
                {
                    //create mysql command  
                    MySqlCommand cmd = new MySqlCommand();
                    //Assign the query using CommandText  
                    cmd.CommandText = query;
                    //Assign the connection using Connection  
                    cmd.Connection = connection;
                    cmd.ExecuteNonQuery();//执行命令
                    //close connection  
                    this.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                NLogger.Error(ex.ToString());
                flag = false;
            }
            return flag;
        }

        //Delete statement  
        public void Delete()
        {
            string query = "DELETE FROM tableinfo WHERE name='John Smith'";

            if (this.OpenConnection() == null)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

        //Select statement  
        public List<string>[] Select()
        {
            string query = "SELECT * FROM tableinfo";

            //Create a list to store the result  
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();

            //Open connection  
            if (this.OpenConnection() == null)
            {
                //Create Command  
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command  
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list  
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["name"] + "");
                    list[2].Add(dataReader["age"] + "");
                }
                //close Data Reader  
                dataReader.Close();
                //close Connection  
                this.CloseConnection();
                //return list to be displayed  
                return list;
            }
            else
            {
                return list;
            }
        }

        //Count statement  
        public int Count()
        {
            string query = "SELECT Count(*) FROM tableinfo";
            int Count = -1;
            //Open Connection  
            if (this.OpenConnection() == null)
            {
                //Create Mysql Command  
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value  
                Count = int.Parse(cmd.ExecuteScalar() + "");
                //close Connection  
                this.CloseConnection();
                return Count;
            }
            else
            {
                return Count;
            }
        }

        //Backup  
        public void Backup()
        {
            try
            {
                DateTime Time = DateTime.Now;
                int year = Time.Year;
                int month = Time.Month;
                int day = Time.Day;
                int hour = Time.Hour;
                int minute = Time.Minute;
                int second = Time.Second;
                int millisecond = Time.Millisecond;

                //Save file to C:\ with the current date as a filename  
                string path;
                path = "C:\\" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
                StreamWriter file = new StreamWriter(path);


                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysqldump";
                psi.RedirectStandardInput = false;
                psi.RedirectStandardOutput = true;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);

                string output;
                output = process.StandardOutput.ReadToEnd();
                file.WriteLine(output);
                process.WaitForExit();
                file.Close();
                process.Close();
            }
            catch
            {
              //  MessageBox.Show("Error , unable to backup!");

                throw new  Exception("Error , unable to backup!");
            }
        }

        //Restore  
        public void Restore()
        {
            try
            {
                //Read file from C:\  
                string path;
                path = "C:\\MySqlBackup.sql";
                StreamReader file = new StreamReader(path);
                string input = file.ReadToEnd();
                file.Close();


                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysql";
                psi.RedirectStandardInput = true;
                psi.RedirectStandardOutput = false;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);
                process.StandardInput.WriteLine(input);
                process.StandardInput.Close();
                process.WaitForExit();
                process.Close();
            }
            catch
            {
               // MessageBox.Show("Error , unable to Restore!");

                throw new Exception("Error , unable to Restore!");


            }
        }
        //Execute Sql File  
        public void ExecuteSQLFile(string fileName)
        {
            string sql = File.ReadAllText(fileName, Encoding.UTF8);
            MySqlCommand myCommand = new MySqlCommand(sql);
            myCommand.Connection = connection;
            if (this.OpenConnection() == null)
            {
                myCommand.ExecuteNonQuery();
                //MessageBox.Show("..........");  
                this.CloseConnection();
            }


        }
        /// <summary>
        /// 用现有的数据库连接执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="connection">一个现有的数据库连接</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {

            try
            {
                MySqlCommand cmd = new MySqlCommand();
                if (this.OpenConnection() == null)
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    this.CloseConnection();
                    return val;
                }
            }
            catch
            {
                //关闭连接，抛出异常
                if (connection.State == ConnectionState.Open)
                    this.CloseConnection();
                return -1;
            }
            return -1;
        }
        /// <summary>
        ///使用现有的SQL事务执行一个sql命令（不返回数据集）
        /// </summary>
        /// <remarks>
        ///举例:
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">一个现有的事务</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                if (this.OpenConnection() == null)
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    this.CloseConnection();
                    return val;
                }
            }
            catch
            {
                //关闭连接，抛出异常
                if (connection.State == ConnectionState.Open)
                    this.CloseConnection();
                return -1;
            }
            return -1;
        }
        /// <summary>
        /// 用执行的数据库连接执行一个返回数据集的sql命令
        /// </summary>
        /// <remarks>
        /// 举例:
        /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>包含结果的读取器</returns>
        public MySqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                if (this.OpenConnection() == null)
                {
                    //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
                    //因此commandBehaviour.CloseConnection 就不会执行
                    try
                    {
                        //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                        //调用 MySqlCommand 的 ExecuteReader 方法
                        MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        //清除参数
                        cmd.Parameters.Clear();
                        this.CloseConnection();
                        return reader;
                    }
                    catch
                    {
                        //关闭连接，抛出异常
                        if (connection.State == ConnectionState.Open)
                            this.CloseConnection();
                        return null;
                    }
                }
            }
            catch
            {
                //关闭连接，抛出异常
                if (connection.State == ConnectionState.Open)
                    this.CloseConnection();
                return null;
            }
            return null;
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns></returns>
        public DataSet GetDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                if (this.OpenConnection() == null)
                {
                    //在这里我们用一个try/catch结构执行sql文本命令/存储过程，
                    //因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
                    try
                    {
                        //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                        //调用 MySqlCommand 的 ExecuteReader 方法
                        MySqlDataAdapter adapter = new MySqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //清除参数
                        cmd.Parameters.Clear();
                        this.CloseConnection();
                        return ds;
                    }
                    catch
                    {
                        //关闭连接，抛出异常
                        if (connection.State == ConnectionState.Open)
                            this.CloseConnection();
                        return null;
                    }
                }
            }
            catch
            {
                //关闭连接，抛出异常
                if (connection.State == ConnectionState.Open)
                    this.CloseConnection();
                return null;
            }
            return null;
        }
        /// <summary>
        /// 准备执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">OleDb连接</param>
        /// <param name="trans">OleDb事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="cmdText">命令文本,例如:Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

    }
}
